﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Collections;
using ilibrary.source.common;

namespace ilibrary.source.model
{
    /// <summary>
    /// Classe donnant acces a la base SqlLite embarque
    /// Cette classe est implémentée en tant que singleton.
    /// </summary>
    class SqlliteDB
    {
        #region Constantes
        // Champs des tables
        private readonly string idFieldName = "id";
        private readonly string ebooksIdFieldName = "ebooks_id";
        private readonly string tagsIdFieldName = "tags_id";
        private readonly string filePathFieldName = "filepath";
        private readonly string titleFieldName = "title";
        private readonly string authorsFieldName = "authors";
        private readonly string publisherFieldName = "publisher";
        private readonly string descriptionFieldName = "description";
        private readonly string isbnFieldName = "isbn";
        private readonly string imageUrlFieldName = "imageurl";
        private readonly string summaryFieldName = "summary";
        private readonly string tagFieldName = "tag";
        private readonly string folderFieldName = "folder";
        private readonly string chapterPathFieldName = "chapterPath";
        private readonly string notesFieldName = "notes";

        // Nom des tables
        private readonly string EBooksTableName = "ebooks";
        private readonly string TagsTableName = "tags";
        private readonly string EBooksTagsTableName = "ebooks_tags";
        private readonly string FoldersTableName = "folders";
        private readonly string PersonalNotesTableName = "personal_notes";
        #endregion

        #region Variables and Properties
        private SQLiteConnection connection;

        public SQLiteConnection SQLConnection
        {
            get
            {
                return connection;
            }
        }

        private static SqlliteDB sqlliteDBInstance = new SqlliteDB();

        public static SqlliteDB Instance
        {
            get
            {
                return sqlliteDBInstance;
            }
        }
        #endregion

        #region Constructors
        protected SqlliteDB()
        {
            //initialise la base de données.
            initDB();
        }
        #endregion

        #region initMethods
        private bool tableExists(string tableName)
        {
            string query = "SELECT name FROM SQLITE_MASTER WHERE type='table' AND name='" + tableName + "'";
            SQLiteDataReader reader = ExecuteQuery(query);
            bool retVal = reader.HasRows;
            reader.Close();
            return retVal;
        }

        /// <summary>
        /// Ouvre une connexion a la db Sqllite. les tables sont initialisées si elles n'existent pas.
        /// Dans tous les cas, le handler vers la DataSource SqlLite est initialisé.
        /// </summary>
        private void initDB()
        {
            //On ouvre la connection a la base Sqllite
            connection = new SQLiteConnection();
            connection.ConnectionString = "Data Source=ilibrarydb.db";
            connection.Open();

            //On crée les tables si elles n'existent pas déjà
            initTables();

            //Suppression des enregistrements inutiles
            cleanDB();
        }

        /// <summary>
        /// Cree les tables de la base de données
        /// </summary>
        private void initTables()
        {
            initTableEbooks();
            initTableTags();
            initTableEBooksTags();
            initTableFolders();
            initTablePersonalNotes();
        }

        /// <summary>
        /// Crée la table ebooks qui contient les infos des ebooks
        /// Champs : 
        /// id
        /// filepath
        /// title
        /// authors
        /// publisher
        /// description
        /// isbn
        /// imageurl
        /// summary
        /// </summary>
        private void initTableEbooks()
        {
            if (tableExists(this.EBooksTableName))
                return;

            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                    "CREATE TABLE ", this.EBooksTableName, " (",
                    this.idFieldName, " integer PRIMARY KEY AUTOINCREMENT, ",
                    this.filePathFieldName, " varchar(255) UNIQUE, ",
                    this.titleFieldName, " varchar(255), ",
                    this.authorsFieldName, " varchar(200), ",
                    this.publisherFieldName, " varchar(100), ",
                    this.descriptionFieldName, " varchar(100), ",
                    this.isbnFieldName, " char(17), ",
                    this.imageUrlFieldName, " varchar(255), ",
                    this.summaryFieldName, " varchar(10000))"));

            ExecuteNonQuery(query.ToString());
        }

        /// <summary>
        /// Crée la table tags qui contient tous les tags définis par l'utilisateur
        /// Champs :
        /// id
        /// tag
        /// </summary>
        private void initTableTags()
        {
            if (tableExists(this.TagsTableName))
                return;

            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                    "CREATE TABLE ", this.TagsTableName, " (",
                    this.idFieldName, " integer PRIMARY KEY AUTOINCREMENT, ",
                    this.tagFieldName, " varchar(20) UNIQUE)"));

            ExecuteNonQuery(query.ToString());

            // Tag Favoris
            query = new StringBuilder();
            query.Append(string.Concat(
                "INSERT INTO ", this.TagsTableName, "(", this.tagFieldName, ") VALUES('')"));
            ExecuteNonQuery(query.ToString());
        }

        /// <summary>
        /// Crée la table ebooks_tags, correspondance entre les ebooks et les tags
        /// Champs :
        /// ebooks_id
        /// tags_id
        /// </summary>
        private void initTableEBooksTags()
        {
            if (tableExists(this.EBooksTagsTableName))
                return;

            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                    "CREATE TABLE ", this.EBooksTagsTableName, " (",
                    this.ebooksIdFieldName, " integer, ",
                    this.tagsIdFieldName, " integer, ",
                    "PRIMARY KEY (", this.ebooksIdFieldName, ", ", this.tagsIdFieldName, "), ",
                    "CONSTRAINT fk_", this.EBooksTagsTableName, "_", this.EBooksTableName,
                    " FOREIGN KEY (", this.ebooksIdFieldName, ")",
                    " REFERENCES ", this.EBooksTableName, "(", this.idFieldName, "), ",
                    "CONSTRAINT fk_", this.EBooksTagsTableName, "_", this.TagsTableName,
                    " FOREIGN KEY (", this.tagsIdFieldName, ")",
                    " REFERENCES ", this.TagsTableName, "(", this.idFieldName, "))"));

            ExecuteNonQuery(query.ToString());
        }

        /// <summary>
        /// Cree la table folders contenant la liste des dossiers à scanner
        /// Champs :
        /// id
        /// folder
        /// </summary>
        private void initTableFolders()
        {
            if (tableExists(this.FoldersTableName))
                return;

            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                    "CREATE TABLE ", this.FoldersTableName, " (",
                    this.idFieldName, " integer PRIMARY KEY AUTOINCREMENT, ",
                    this.folderFieldName, " varchar(255) UNIQUE ", ")"));

            ExecuteNonQuery(query.ToString());
        }

        /// <summary>
        /// Cree la table personal_notes contenant les notes prises par l'utilisateur
        /// Champs :
        /// ebooks_id
        /// chapter_id
        /// notes
        /// </summary>
        private void initTablePersonalNotes()
        {
            if (tableExists(this.PersonalNotesTableName))
                return;

            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                    "CREATE TABLE ", this.PersonalNotesTableName, " (",
                    this.ebooksIdFieldName, " integer, ",
                    this.chapterPathFieldName, " varchar(255), ",
                    this.notesFieldName, " varchar(10000), ",
                    "PRIMARY KEY (", this.ebooksIdFieldName, ",", this.chapterPathFieldName, "), ",
                    "CONSTRAINT fk_", this.PersonalNotesTableName, "_", this.EBooksTableName,
                    " FOREIGN KEY (", this.ebooksIdFieldName, ")",
                    " REFERENCES ", this.EBooksTableName, "(", this.idFieldName, "))"));

            ExecuteNonQuery(query.ToString());
        }
        #endregion

        #region methods for query execution
        /// <summary>
        /// Execute une requete ne renvoyant pas de resultat.
        /// Ex : CREATE, INSERT,....
        /// </summary>
        /// <param name="query"></param>
        private void ExecuteNonQuery(string query)
        {
            SQLiteCommand command = connection.CreateCommand();
            command.CommandText = query;
            command.ExecuteNonQuery();
        }

        /// <summary>
        /// Execute une requete qui renvoie un object unique.
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        private object ExecuteScalarQuery(string query)
        {
            SQLiteCommand command = connection.CreateCommand();
            command.CommandText = query;
            return command.ExecuteScalar();
        }

        /// <summary>
        /// Execute une requete et renvoie un reader permettant de lire les résultats.
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        private SQLiteDataReader ExecuteQuery(string query)
        {
            SQLiteCommand command = connection.CreateCommand();
            command.CommandText = query;
            return command.ExecuteReader();
        }
        #endregion

        #region FolderTable
        #region update methods
        /// <summary>
        /// Insere un nouveau repertoire à scanner si celui ci n'existe pas encore dans la base.
        /// On vérifie que le chemin passé est bien celui d'un répertoire existant sur le disque.
        /// </summary>
        /// <param name="folderPath"></param>
        public void insertNewFolder(string folderPath)
        {
            if (!string.IsNullOrEmpty(folderPath) && System.IO.Directory.Exists(folderPath))
            {
                //On verifie qu'un de ces dossiers parents ne figure pas déjà dans notre liste
                foreach (string dir in getAllFolders())
                {
                    if (folderPath.StartsWith(dir))
                        return; //pas besoin de le rajouter
                }

                StringBuilder query = new StringBuilder();
                query.Append(string.Concat(
                    "INSERT INTO ", this.FoldersTableName,
                    " (", this.folderFieldName, ") VALUES('", Common.QuoteEscape(folderPath), "')"));

                ExecuteNonQuery(query.ToString());

            }
        }
        /// <summary>
        /// Supprime de la collection, un repertoire précis.
        /// </summary>
        /// <param name="folderPath"></param>
        public void deleteFolder(string folderPath)
        {
            if (!string.IsNullOrEmpty(folderPath))
            {
                StringBuilder query = new StringBuilder();

                //supprimer les ebooks du dossier
                query.Append(string.Concat(
                    "DELETE FROM ", this.EBooksTableName,
                    " WHERE ", this.filePathFieldName, " LIKE '", Common.QuoteEscape(folderPath), "%'"));
                ExecuteNonQuery(query.ToString());

                //supprimer le dossier
                query = new StringBuilder();
                query.Append(string.Concat(
                    "DELETE FROM ", this.FoldersTableName,
                    " WHERE ", this.folderFieldName, "='", Common.QuoteEscape(folderPath),"'"));
                ExecuteNonQuery(query.ToString());
            }
        }
        #endregion

        #region query methods
        /// <summary>
        /// Retourne la liste de tous les dossiers de la collection
        /// </summary>
        /// <returns></returns>
        public List<string> getAllFolders()
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat("SELECT ", folderFieldName, " FROM ", FoldersTableName));
            SQLiteDataReader reader = ExecuteQuery(query.ToString());
            List<string> retVal = new List<string>();
            while (reader.Read())
            {
                retVal.Add(reader.GetString(0));
            }
            reader.Close();
            return retVal;
        }
        #endregion
        #endregion

        #region EBookTable
        #region update methods
        /// <summary>
        /// Insere dans la base un ebook à partire du chemin du fichier.
        /// </summary>
        /// <param name="path"></param>
        public void insertEbook(string path)
        {
            if (!string.IsNullOrEmpty(path))
            {
                StringBuilder query = new StringBuilder();
                query.Append(string.Concat(
                    "INSERT INTO ", this.EBooksTableName,
                    " (", this.filePathFieldName, ") VALUES('", Common.QuoteEscape(path), "')"));
                ExecuteNonQuery(query.ToString());
            }
        }
        /// <summary>
        /// Supprime un ebook de la base
        /// </summary>
        /// <param name="id"></param>
        public void deleteEbook(int id)
        {
            clearTagsForEbook(id);
            clearPersonalNotesForEbook(id);

            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "DELETE FROM ", this.EBooksTableName,
                " WHERE ", this.idFieldName, "='", id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Met à jour tous les champs d'un ebook
        /// </summary>
        /// <param name="id">Id de l'EBook à mettre à jour</param>
        /// <param name="filepath">New Filepath</param>
        /// <param name="title">New Title</param>
        /// <param name="authors">New Authors</param>
        /// <param name="publisher">New Publisher</param>
        /// <param name="description">New Description</param>
        /// <param name="isbn">New ISBN</param>
        /// <param name="imageurl">New ImageUrl</param>
        /// <param name="summary">New Summary</param>
        /// <param name="tags">mettre à null pour ne pas mettre à jour les tags</param>
        public void updateEBook(int id, string filepath, string title,
            string authors, string publisher, string description,
            string isbn, string imageurl, string summary, List<string> tags)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "UPDATE ", this.EBooksTableName, " SET ",
                this.filePathFieldName, "='", Common.QuoteEscape(filepath), "', ",
                this.titleFieldName, "='", Common.QuoteEscape(title), "', ",
                this.authorsFieldName, "='", Common.QuoteEscape(authors), "', ",
                this.publisherFieldName, "='", Common.QuoteEscape(publisher), "', ",
                this.descriptionFieldName, "='", Common.QuoteEscape(description), "', ",
                this.isbnFieldName, "='", Common.QuoteEscape(isbn), "', ",
                this.imageUrlFieldName, "='", Common.QuoteEscape(imageurl), "', ",
                this.summaryFieldName, "='", Common.QuoteEscape(summary),
                "' WHERE ", this.idFieldName, "=", id));
            ExecuteNonQuery(query.ToString());

            if (tags != null)
            {
                clearTagsForEbook(id);
                for (int i = 0; i < tags.Count; i++)
                    insertEbookTag(id, selectTagId(tags[i]));
            }
        }
        /// <summary>
        /// Met à jour le champ path de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <param name="path"></param>
        public void updateEbookPath(int id, string filepath)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat("UPDATE ", this.EBooksTableName,
                " SET ", this.filePathFieldName, "='", Common.QuoteEscape(filepath), "'",
                " WHERE ", this.idFieldName, "='", id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Met à jour le champ title de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <param name="title"></param>
        public void updateEbookTitle(int id, string title)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "UPDATE ", this.EBooksTableName,
                " SET ", this.titleFieldName, "=", title == null ? "NULL" : "'"+Common.QuoteEscape(title)+"'", 
                " WHERE ", this.idFieldName, "='", id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Met à jour le champ authors de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <param name="authors"></param>
        public void updateEbookAuthors(int id, string authors)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "UPDATE ", this.EBooksTableName,
                " SET ", this.authorsFieldName, "=", authors == null ? "NULL" : "'"+Common.QuoteEscape(authors)+"'",
                " WHERE ", this.idFieldName, "='", id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Met à jour le champ publisher de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <param name="publisher"></param>
        public void updateEbookPublisher(int id, string publisher)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "UPDATE ", this.EBooksTableName,
                " SET ", this.publisherFieldName, "=", publisher == null ? "NULL" : "'"+Common.QuoteEscape(publisher)+"'", 
                " WHERE ", this.idFieldName, "='", id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Met à jour le champ description de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <param name="description"></param>
        public void updateEbookDescription(int id, string description)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "UPDATE ", this.EBooksTableName,
                " SET ", this.descriptionFieldName, "=", description == null ? "NULL" : "'"+Common.QuoteEscape(description)+"'", 
                " WHERE ", this.idFieldName, "='", id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Met à jour le champ isbn de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <param name="isbn"></param>
        public void updateEbookISBN(int id, string isbn)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "UPDATE ", this.EBooksTableName,
                " SET ", this.isbnFieldName, "=", isbn == null ? "NULL" : "'"+Common.QuoteEscape(isbn)+"'", 
                " WHERE ", this.idFieldName, "='", id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Met à jour le champ imageurl de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <param name="imageurl"></param>
        public void updateEbookImageUrl(int id, string imageurl)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "UPDATE ", this.EBooksTableName,
                " SET ", this.imageUrlFieldName, "=", imageurl == null ? "NULL" : "'"+Common.QuoteEscape(imageurl)+"'", 
                " WHERE ", this.idFieldName, "='", id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Met à jour le champ summary de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <param name="summary"></param>
        public void updateEbookSummary(int id, string summary)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "UPDATE ", this.EBooksTableName,
                " SET ", this.summaryFieldName, "=", summary == null ? "NULL" : "'"+Common.QuoteEscape(summary)+"'",
                " WHERE ", this.idFieldName, "='", id, "'"));
            ExecuteNonQuery(query.ToString());
        }

        public void clearEbookCollection()
        {
            StringBuilder query =
                new StringBuilder(string.Concat("DELETE FROM ", this.EBooksTagsTableName));
            ExecuteNonQuery(query.ToString());

            query =
                new StringBuilder(string.Concat("DELETE FROM ", this.PersonalNotesTableName));
            ExecuteNonQuery(query.ToString());

            query =
                new StringBuilder(string.Concat("DELETE FROM ", this.EBooksTableName));
            ExecuteNonQuery(query.ToString());
        }
        #endregion

        #region query methods
        public int ebookCount()
        {
            StringBuilder query =
                new StringBuilder(string.Concat("SELECT COUNT(", this.idFieldName, ") FROM ", this.EBooksTableName));
            return int.Parse(ExecuteScalarQuery(query.ToString()).ToString());
        }
        /// <summary>
        /// Retourne true si un ebook avec ce chemin de fichier existe. False sinon.
        /// </summary>
        /// <param name="path"></param>
        public bool ebookExists(string path)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.filePathFieldName, " FROM ", this.EBooksTableName,
                " WHERE ", this.filePathFieldName, "='", Common.QuoteEscape(path), "'"));

            SQLiteDataReader reader = ExecuteQuery(query.ToString());
            bool retVal = reader.HasRows;
            reader.Close();
            return retVal;
        }
        /// <summary>
        /// Sélectionne le titre de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string selectEBookTitle(int id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.titleFieldName, " FROM ", this.EBooksTableName,
                " WHERE ", this.idFieldName, "='", id, "'"));

            object title = ExecuteScalarQuery(query.ToString());
            return title.ToString();
        }

        /// <summary>
        /// Sélectionne l'auteur de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string selectEBookAuthors(int id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.authorsFieldName, " FROM ", this.EBooksTableName,
                " WHERE ", this.idFieldName, "='", id, "'"));

            object authors = this.ExecuteScalarQuery(query.ToString());
            return authors.ToString();
        }
        /// <summary>
        /// Sélectionne l'édition de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string selectEBookPublisher(int id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.publisherFieldName, " FROM ", this.EBooksTableName,
                " WHERE ", this.idFieldName, "='", id, "'"));

            object publisher = this.ExecuteScalarQuery(query.ToString());
            return publisher.ToString();
        }
        /// <summary>
        /// Sélectionne la description de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string selectEBookDescription(int id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.descriptionFieldName, " FROM ", this.EBooksTableName,
                " WHERE ", this.idFieldName, "='", id, "'"));

            object description = this.ExecuteScalarQuery(query.ToString());
            return description.ToString();
        }
        /// <summary>
        /// Sélectionne l'ISBN de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string selectEBookISBN(int id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.isbnFieldName, " FROM ", this.EBooksTableName,
                " WHERE ", this.idFieldName, "='", id, "'"));

            object isbn = this.ExecuteScalarQuery(query.ToString());
            return isbn.ToString();
        }
        /// <summary>
        /// Sélectionne l'url de l'image de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string selectEBookImageUrl(int id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.imageUrlFieldName, " FROM ", this.EBooksTableName,
                " WHERE ", this.idFieldName, "='", id, "'"));

            object imageUrl = this.ExecuteScalarQuery(query.ToString());
            return imageUrl.ToString();
        }
        /// <summary>
        /// Sélectionne la résumé de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string selectEBookSummary(int id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.summaryFieldName, " FROM ", this.EBooksTableName,
                " WHERE ", this.idFieldName, "='", id, "'"));

            object summary = this.ExecuteScalarQuery(query.ToString());
            return summary.ToString();
        }
        /// <summary>
        /// Sélectionne les tags de l'ebook dont l'id est spécifié
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<string> selectTagsForEBook(int id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT DISTINCT ", this.tagFieldName,
                " FROM ", this.TagsTableName, ",", this.EBooksTagsTableName,
                " WHERE ", this.ebooksIdFieldName, "='", id, "'",
                " AND ", this.tagsIdFieldName, "=", this.idFieldName));

            SQLiteDataReader tags = this.ExecuteQuery(query.ToString());
            List<string> toReturn = new List<string>(tags.RecordsAffected);

            while (tags.Read())
            {
                toReturn.Add(tags.GetString(0));
            }
            tags.Close();
            return toReturn;
        }

        public IEbook GetEbook(int id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.idFieldName, ",", this.filePathFieldName,
                " FROM " + this.EBooksTableName + " WHERE "+this.idFieldName+"="+id));
            SQLiteDataReader reader = SqlliteDB.Instance.ExecuteQuery(query.ToString());
            if (reader.Read())
            {
                
                string path = reader.GetString(1);
                reader.Close();

                IEbookHandler handler = Program.HandlerFactory.GetHandlerFromPath(path);
                if (handler != null)
                {
                    IEbook ebook = handler.createEbook(id, path);
                    return ebook;
                }
            }
            return null;
        }

        public void loadAllEbooks(Dictionary<int, IEbook> ebooksView)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.idFieldName, ",", this.filePathFieldName,
                " FROM " + this.EBooksTableName));
            SQLiteDataReader reader = SqlliteDB.Instance.ExecuteQuery(query.ToString());
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                string path = reader.GetString(1);

                IEbookHandler handler = Program.HandlerFactory.GetHandlerFromPath(path);
                if (handler != null)
                {
                    IEbook ebook = handler.createEbook(id, path);
                    ebooksView.Add(id, ebook);
                }
            }
            reader.Close();
        }

        public void loadFavouritesEbooks(Dictionary<int, IEbook> ebooksView)
        {
            int favouritesId = selectTagId("");

            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.idFieldName, ",", this.filePathFieldName,
                " FROM ", this.EBooksTableName, ",", this.EBooksTagsTableName,
                " WHERE ", this.idFieldName, "=", this.ebooksIdFieldName,
                " AND ", this.tagsIdFieldName, "='", favouritesId, "'"));
            SQLiteDataReader reader = SqlliteDB.Instance.ExecuteQuery(query.ToString());
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                string path = reader.GetString(1);

                IEbookHandler handler = Program.HandlerFactory.GetHandlerFromPath(path);
                if (handler != null)
                {
                    IEbook ebook = handler.createEbook(id, path);
                    ebooksView.Add(id, ebook);
                }
            }
            reader.Close();
        }

        public void loadEbooksWithTags(Dictionary<int, IEbook> ebooksView, List<int> tags)
        {
            if (tags.Count == 0)
            {
                loadAllEbooks(ebooksView);
                return;
            }

            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT DISTINCT ", this.idFieldName, ",", this.filePathFieldName,
                " FROM ", this.EBooksTableName, ",", this.EBooksTagsTableName,
                " WHERE ", this.idFieldName, "=", this.ebooksIdFieldName,
                " AND (", this.tagsIdFieldName, "='", tags[0], "'"));
            for (int i = 1; i < tags.Count; i++)
                query.Append(string.Concat(" OR ",this.tagsIdFieldName, "='", tags[i], "'"));
            query.Append(")");
            SQLiteDataReader reader = SqlliteDB.Instance.ExecuteQuery(query.ToString());
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                string path = reader.GetString(1);

                IEbookHandler handler = Program.HandlerFactory.GetHandlerFromPath(path);
                if (handler != null)
                {
                    IEbook ebook = handler.createEbook(id, path);
                    ebooksView.Add(id, ebook);
                }
            }
            reader.Close();
        }

        public void loadEbooksType(Dictionary<int, IEbook> ebooksView, string type)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT DISTINCT ", this.idFieldName, ",", this.filePathFieldName,
                " FROM ", this.EBooksTableName,
                " WHERE ", this.filePathFieldName, " LIKE '%.", type, "'"));
            SQLiteDataReader reader = SqlliteDB.Instance.ExecuteQuery(query.ToString());
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                string path = reader.GetString(1);

                IEbookHandler handler = Program.HandlerFactory.GetHandlerFromPath(path);
                if (handler != null)
                {
                    IEbook ebook = handler.createEbook(id, path);
                    ebooksView.Add(id, ebook);
                }
            }
            reader.Close();
        }
        #endregion
        #endregion

        #region Search Methods



        /// <summary>
        /// Retourne la liste des ids d'ebooks ayant pour recherche search
        /// Une liste vide est retounée si aucune occurence n'est trouvée
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public List<int> searchForAll(string search)
        {
            List<int> ids = new List<int>();
            string query = " SELECT " + this.idFieldName + " FROM " + this.EBooksTableName;
            query += " WHERE " + this.titleFieldName + " LIKE '% " + Common.QuoteEscape(search) + " %' ";
            query += " UNION ";
            query += " SELECT " + this.idFieldName + " FROM " + this.EBooksTableName;
            query += " WHERE " + this.authorsFieldName + " LIKE '% " + Common.QuoteEscape(search) + " %' ";
            query += " UNION ";
             
            query += " SELECT " + this.idFieldName + " FROM " + this.EBooksTableName;
            query += " WHERE " + this.descriptionFieldName + " LIKE '% " + Common.QuoteEscape(search) + " %' ";
            query += " UNION ";
               
            query += " SELECT " + this.idFieldName + " FROM " + this.EBooksTableName;
            query += " WHERE " + this.publisherFieldName + " LIKE '% " + Common.QuoteEscape(search) + " %' ";
            query += " UNION "; 
                
            query += " SELECT " + this.idFieldName + " FROM " + this.EBooksTableName;
            query += " WHERE " + this.isbnFieldName + " LIKE '% " + Common.QuoteEscape(search) + " %' ";
           

             
            SQLiteDataReader reader = ExecuteQuery(query);
            while (reader.Read())
            {
                ids.Add(reader.GetInt32(0));
            }
            reader.Close();
            return ids;
        }



        /// <summary>
        /// Retourne la liste des ids d'ebooks ayant pour auteur authors
        /// Une liste vide est retounée si aucune occurence n'est trouvée
        /// </summary>
        /// <param name="authors"></param>
        /// <returns></returns>
        public List<int> searchForAuthors(string authors)
        {
            List<int> ids = new List<int>();
            string query = "SELECT " + this.idFieldName + " FROM " + this.EBooksTableName;
            query += " WHERE " + this.authorsFieldName + "='" + Common.QuoteEscape(authors) + "' ";
            SQLiteDataReader reader = ExecuteQuery(query);
            while (reader.Read())
            {
                ids.Add(reader.GetInt32(0));
            }
            reader.Close();
            return ids;
        }


        /// <summary>
        /// Retourne la liste des ids d'ebooks ayant pour titre title
        /// Une liste vide est retounée si aucune occurence n'est trouvée
        /// </summary>
        /// <param name="title"></param>
        /// <returns></returns>
        public List<int> searchForTitle(string title)
        {
            List<int> ids = new List<int>();
            string query = "SELECT " + this.idFieldName + " FROM " + this.EBooksTableName;
            query += " WHERE " + this.titleFieldName + "='" + Common.QuoteEscape(title) + "' ";
            SQLiteDataReader reader = ExecuteQuery(query);
            while (reader.Read())
            {
                ids.Add(reader.GetInt32(0));
            }
            reader.Close();
            return ids;
        }


        /// <summary>
        /// Retourne la liste des ids d'ebooks ayant pour editeur publisher
        /// Une liste vide est retounée si aucune occurence n'est trouvée
        /// </summary>
        /// <param name="publisher"></param>
        /// <returns></returns>
        public List<int> searchForPublisher(string publisher)
        {
            List<int> ids = new List<int>();
            string query = "SELECT " + this.idFieldName + " FROM " + this.EBooksTableName;
            query += " WHERE " + this.publisherFieldName + "='" + Common.QuoteEscape(publisher) + "' ";
            SQLiteDataReader reader = ExecuteQuery(query);
            while (reader.Read())
            {
                ids.Add(reader.GetInt32(0));
            }
            reader.Close();
            return ids;
        }

        /// <summary>
        /// Retourne la liste des ids d'ebooks ayant pour description description
        /// Une liste vide est retounée si aucune occurence n'est trouvée
        /// </summary>
        /// <param name="description"></param>
        /// <returns></returns>
        public List<int> searchForDescription(string description)
        {
            List<int> ids = new List<int>();
            string query = "SELECT " + this.idFieldName + " FROM " + this.EBooksTableName;
            query += " WHERE " + this.descriptionFieldName + "='" + Common.QuoteEscape(description) + "' ";
            SQLiteDataReader reader = ExecuteQuery(query);
            while (reader.Read())
            {
                ids.Add(reader.GetInt32(0));
            }
            reader.Close();
            return ids;
        }

        /// <summary>
        /// Retourne la liste des ids d'ebooks ayant pour isbn isbn
        /// Une liste vide est retounée si aucune occurence n'est trouvée
        /// </summary>
        /// <param name="isbn"></param>
        /// <returns></returns>
        public List<int> searchForIsbn(string isbn)
        {
            List<int> ids = new List<int>();
            string query = "SELECT " + this.idFieldName + " FROM " + this.EBooksTableName;
            query += " WHERE " + this.isbnFieldName + "='" + Common.QuoteEscape(isbn) + "' ";
            SQLiteDataReader reader = ExecuteQuery(query);
            while (reader.Read())
            {
                ids.Add(reader.GetInt32(0));
            }
            reader.Close();
            return ids;
        }
        #endregion

        #region TagTable
        #region update methods
        /// <summary>
        /// Insère dans la base un nouveau tag
        /// </summary>
        /// <param name="tag"></param>
        public void insertTag(string tag)
        {
            if (!string.IsNullOrEmpty(tag))
            {
                StringBuilder query = new StringBuilder();
                query.Append(string.Concat(
                    "INSERT INTO ", this.TagsTableName, "(", this.tagFieldName, ") VALUES('", Common.QuoteEscape(tag), "')"));
                ExecuteNonQuery(query.ToString());
            }
        }
        /// <summary>
        /// Supprime un tag (ainsi que les correspondances tag / ebook pour celui-ci)
        /// </summary>
        /// <param name="id"></param>
        public void deleteTag(int id)
        {
            clearEbooksForTag(id);

            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "DELETE FROM ", this.TagsTableName,
                " WHERE ", this.idFieldName, "='", id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Met à jour un tag (renommage)
        /// </summary>
        /// <param name="id"></param>
        /// <param name="tag"></param>
        public void updateTag(int id, string tag)
        {
            if (!string.IsNullOrEmpty(tag))
            {
                StringBuilder query = new StringBuilder();
                query.Append(string.Concat(
                    "UPDATE ", this.TagsTableName,
                    " SET ", this.tagFieldName, "='", Common.QuoteEscape(tag), "'",
                    " WHERE ", this.idFieldName, "='", id, "'"));
                ExecuteNonQuery(query.ToString());
            }
        }
        #endregion

        #region query methods
        /// <summary>
        /// Sélectionne l'id du tag indiqué
        /// </summary>
        /// <param name="tag"></param>
        /// <returns></returns>
        public int selectTagId(string tag)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.idFieldName, " FROM ", this.TagsTableName,
                " WHERE ", this.tagFieldName, "='", Common.QuoteEscape(tag), "'"));

            return int.Parse(ExecuteScalarQuery(query.ToString()).ToString());
        }
        /// <summary>
        /// Récupère tous les tags sauf Favoris (id 1)
        /// </summary>
        /// <returns></returns>
        public List<string> selectAllTag()
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.tagFieldName, " FROM ", this.TagsTableName));

            List<string> list = new List<string>();
            SQLiteDataReader reader = ExecuteQuery(query.ToString());

            while (reader.Read())
                list.Add(reader.GetString(0));

            list.RemoveAt(0); // Suppression de Favoris

            return list;
        }

        public Dictionary<int, string> selectAllTagWithId()
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.idFieldName, ",", this.tagFieldName, " FROM ", this.TagsTableName, " ORDER BY ", this.tagFieldName));

            Dictionary<int, string> list = new Dictionary<int, string>();
            SQLiteDataReader reader = ExecuteQuery(query.ToString());

            while (reader.Read())
                list.Add(reader.GetInt32(0), reader.GetString(1));

            list.Remove(selectTagId("")); // Suppression de Favoris

            return list;
        }
        #endregion
        #endregion

        #region EBooksTagsTable
        #region update methods
        /// <summary>
        /// Insère une correspondance entre un ebook et un tag dans la table EBooks_Tags
        /// </summary>
        /// <param name="ebook_id"></param>
        /// <param name="tag_id"></param>
        public void insertEbookTag(int ebook_id, int tag_id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "INSERT INTO ", this.EBooksTagsTableName,
                " VALUES('", ebook_id, "','", tag_id, "')"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Supprime une correspondance tag / id
        /// </summary>
        /// <param name="ebook_id"></param>
        /// <param name="tag_id"></param>
        public void deleteEbookTag(int ebook_id, int tag_id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "DELETE FROM ", this.EBooksTagsTableName,
                " WHERE ", this.ebooksIdFieldName, "='", ebook_id, "'",
                " AND ", this.tagsIdFieldName, "='", tag_id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Supprime tous les tags pour l'ebook dont l'id est indiqué
        /// </summary>
        /// <param name="id">id de l'ebook</param>
        private void clearTagsForEbook(int id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "DELETE FROM ", this.EBooksTagsTableName,
                " WHERE ", this.ebooksIdFieldName, "='", id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Supprime tous les correspondances tag / ebook pour le tag dont l'id est indiqué
        /// </summary>
        /// <param name="id">id du tag</param>
        private void clearEbooksForTag(int id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "DELETE FROM ", this.EBooksTagsTableName,
                " WHERE ", this.tagsIdFieldName, "='", id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        #endregion

        #region query methods
        public bool ebookTagExist(int ebookId, int tagId)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.ebooksIdFieldName, " FROM ", this.EBooksTagsTableName,
                " WHERE ", this.ebooksIdFieldName, "='", ebookId, "'",
                " AND ", this.tagsIdFieldName, "='", tagId, "'"));

            SQLiteDataReader reader = ExecuteQuery(query.ToString());
            bool retVal = reader.HasRows;
            reader.Close();
            return retVal;
        }
        #endregion
        #endregion

        #region PersonalNotesTable
        #region personal_notes update methods
        /// <summary>
        /// Vérifie si une note existe pour un chapitre d'un ebook
        /// </summary>
        /// <param name="ebook_id"></param>
        /// <param name="chapter_path"></param>
        /// <returns></returns>
        public bool notesExist(int ebook_id, string chapter_path)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.notesFieldName, " FROM ", this.PersonalNotesTableName,
                " WHERE ", this.ebooksIdFieldName, "='", ebook_id, "'",
                " AND ", this.chapterPathFieldName, "='", Common.QuoteEscape(chapter_path), "'"));

            SQLiteDataReader reader = ExecuteQuery(query.ToString());
            bool retVal = reader.HasRows;
            reader.Close();
            return retVal;
        }
        /// <summary>
        /// Ajoute une nouvelle note en base
        /// </summary>
        /// <param name="ebook_id"></param>
        /// <param name="chapter_path"></param>
        /// <param name="personal_notes"></param>
        public void insertPersonalNotes(int ebook_id, string chapter_path, string personal_notes)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "INSERT INTO ", this.PersonalNotesTableName,
                " VALUES('", ebook_id, "','", Common.QuoteEscape(chapter_path), "','", Common.QuoteEscape(personal_notes), "')"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Supprime une note en base
        /// </summary>
        /// <param name="ebook_id"></param>
        /// <param name="chapter_path"></param>
        public void deletePersonalNotes(int ebook_id, string chapter_path)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "DELETE FROM ", this.PersonalNotesTableName,
                " WHERE ", this.ebooksIdFieldName, "='", ebook_id, "'",
                " AND ", this.chapterPathFieldName, "='", Common.QuoteEscape(chapter_path), "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Met à jour une note
        /// </summary>
        /// <param name="ebook_id"></param>
        /// <param name="chapter_path"></param>
        /// <param name="personal_notes"></param>
        public void updatePersonalNotes(int ebook_id, string chapter_path, string personal_notes)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "UPDATE ", this.PersonalNotesTableName,
                " SET ", this.notesFieldName, "='", Common.QuoteEscape(personal_notes), "'",
                " WHERE ", this.ebooksIdFieldName, "='", ebook_id, "'",
                " AND ", this.chapterPathFieldName, "='", Common.QuoteEscape(chapter_path), "'"));
            ExecuteNonQuery(query.ToString());
        }
        /// <summary>
        /// Supprime toutes les notes pour un eBook donné
        /// </summary>
        /// <param name="ebook_id"></param>
        private void clearPersonalNotesForEbook(int ebook_id)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "DELETE FROM ", this.PersonalNotesTableName,
                " WHERE ", this.ebooksIdFieldName, "='", ebook_id, "'"));
            ExecuteNonQuery(query.ToString());
        }
        #endregion

        #region query for personalNotes
        /// <summary>
        /// Récupère les notes pour un chapitre d'un EBook
        /// </summary>
        /// <param name="ebook_id"></param>
        /// <param name="chapter_path"></param>
        /// <returns></returns>
        public string selectPersonalNotesNotes(int ebook_id, string chapter_path)
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "SELECT ", this.notesFieldName, " FROM ", this.PersonalNotesTableName,
                " WHERE ", this.ebooksIdFieldName, "='", ebook_id, "'",
                " AND ", this.chapterPathFieldName, "='", Common.QuoteEscape(chapter_path), "'"));

            object toReturn = ExecuteScalarQuery(query.ToString());
            if (toReturn == null)
                return string.Empty;
            else
                return (string)toReturn;
        }
        #endregion
        #endregion

        #region clean db methods
        private void cleanDB()
        {
            cleanPersonalNotesTable();
        }

        private void cleanPersonalNotesTable()
        {
            StringBuilder query = new StringBuilder();
            query.Append(string.Concat(
                "DELETE FROM ", this.PersonalNotesTableName,
                " WHERE ", this.notesFieldName, "=''"));
            ExecuteNonQuery(query.ToString());
        }
        #endregion
    }
}
